﻿Imports System.Text
Imports System.Collections
Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Transactions
Imports System.Threading
Imports System.Data.Common

Public Class SQLHelper
    Public Enum DBCompareType
        Equal = 0
        NotEqual = 1
        Larger = 2
        Smaller = 3
        LargerNEqual = 4
        SmallerNEqual = 5
        [IS] = 6
        [ISNOT] = 7
        [LIKE] = 8
    End Enum

    Public Enum DBDataFormula
        GETDATE = 0
        GETID = 1
        NULL = 2
    End Enum

    Public Enum DBDataType
        [String] = 0
        [Integer] = 1
        [Date] = 2
        Formula = 3
        DBColumn = 4
        SQLFormula = 5
    End Enum

    Public Enum DBLinkage
        [AND] = 0
        [OR] = 1
    End Enum

    Public Enum TypeOfDatabase
        MSSQL = 0
        OleDB = 1
        ODBC = 2
    End Enum

    Public Class DatabaseParameter
#Region "Fields"
        Public ASC As Boolean
        Public DataCompare As DBCompareType
        Private DataCompareStr As String()
        Public DataFormula As DBDataFormula
        Private DataFormulaStr As String()
        Public DataLinkage As DBLinkage
        Private DataLinkageStr As String()
        Public DataType As DBDataType
        Private Field As String
        Public IsNullable As Boolean
        Public IsUnicode As Boolean
        Public SortBy As Boolean
        Private StringPattern As String
        Private Value As String
#End Region

#Region "Properties"
        Public ReadOnly Property FieldName() As String
            Get
                Return Me.Field
            End Get
        End Property

        Public ReadOnly Property FieldValue() As String
            Get
                Return (If((Me.DataType = DBDataType.Formula), Me.DataFormulaStr(CInt(Me.DataFormula)), (If((Me.Value.Length > 0), Me.Value, Me.StringPattern))))
            End Get
        End Property

        Public ReadOnly Property ParamCompare() As String
            Get
                Return (" " & Me.DataCompareStr(CInt(Me.DataCompare)) & " ")
            End Get
        End Property

        Public ReadOnly Property ParamLinkage() As String
            Get
                Return (" " & Me.DataLinkageStr(CInt(Me.DataLinkage)) & " ")
            End Get
        End Property

        Public ReadOnly Property ParamType() As DBDataType
            Get
                Return Me.DataType
            End Get
        End Property
#End Region

#Region "Methods"
        Public Sub New(ByVal Field As String, ByVal DataFormula As DBDataFormula)
            Me.Field = String.Empty
            Me.Value = String.Empty
            Me.StringPattern = String.Empty
            Me.DataCompareStr = New String() {" = ", " <> ", " > ", " < ", " >= ", " <= ", _
             " IS ", " IS NOT ", " LIKE "}
            Me.DataLinkageStr = New String() {" AND ", " OR "}
            Me.DataFormulaStr = New String() {" GETDATE() ", " GETID() ", " NULL "}
            Me.IsUnicode = False
            Me.IsNullable = False
            Me.DataType = DBDataType.[String]
            Me.DataCompare = DBCompareType.Equal
            Me.DataLinkage = DBLinkage.[AND]
            Me.SortBy = False
            Me.ASC = True
            Me.Field = Field
            Me.DataFormula = DataFormula
            Me.DataType = DBDataType.Formula
        End Sub

        Public Sub New(ByVal Field As String, ByVal TargetDateTime As DateTime)
            Me.Field = String.Empty
            Me.Value = String.Empty
            Me.StringPattern = String.Empty
            Me.DataCompareStr = New String() {" = ", " <> ", " > ", " < ", " >= ", " <= ", _
             " IS ", " IS NOT ", " LIKE "}
            Me.DataLinkageStr = New String() {" AND ", " OR "}
            Me.DataFormulaStr = New String() {" GETDATE() ", " GETID() ", " NULL "}
            Me.IsUnicode = False
            Me.IsNullable = False
            Me.DataType = DBDataType.[String]
            Me.DataCompare = DBCompareType.Equal
            Me.DataLinkage = DBLinkage.[AND]
            Me.SortBy = False
            Me.ASC = True
            Me.Field = Field
            Me.Value = TargetDateTime.ToString()
        End Sub

        Public Sub New(ByVal Field As String, ByVal Value As String)
            Me.Field = String.Empty
            Me.Value = String.Empty
            Me.StringPattern = String.Empty
            Me.DataCompareStr = New String() {" = ", " <> ", " > ", " < ", " >= ", " <= ", _
             " IS ", " IS NOT ", " LIKE "}
            Me.DataLinkageStr = New String() {" AND ", " OR "}
            Me.DataFormulaStr = New String() {" GETDATE() ", " GETID() ", " NULL "}
            Me.IsUnicode = False
            Me.IsNullable = False
            Me.DataType = DBDataType.[String]
            Me.DataCompare = DBCompareType.Equal
            Me.DataLinkage = DBLinkage.[AND]
            Me.SortBy = False
            Me.ASC = True
            Me.Field = Field
            Me.Value = Value
        End Sub

        Public Sub New(ByVal Field As String, ByVal TargetDateTime As DateTime, ByVal IsNullable As Boolean)
            Me.Field = String.Empty
            Me.Value = String.Empty
            Me.StringPattern = String.Empty
            Me.DataCompareStr = New String() {" = ", " <> ", " > ", " < ", " >= ", " <= ", _
             " IS ", " IS NOT ", " LIKE "}
            Me.DataLinkageStr = New String() {" AND ", " OR "}
            Me.DataFormulaStr = New String() {" GETDATE() ", " GETID() ", " NULL "}
            Me.IsUnicode = False
            Me.IsNullable = False
            Me.DataType = DBDataType.[String]
            Me.DataCompare = DBCompareType.Equal
            Me.DataLinkage = DBLinkage.[AND]
            Me.SortBy = False
            Me.ASC = True
            Me.Field = Field
            Me.Value = TargetDateTime.ToString()
            Me.IsNullable = IsNullable
        End Sub

        Public Sub New(ByVal Field As String, ByVal Value As String, ByVal DataType As DBDataType)
            Me.Field = String.Empty
            Me.Value = String.Empty
            Me.StringPattern = String.Empty
            Me.DataCompareStr = New String() {" = ", " <> ", " > ", " < ", " >= ", " <= ", _
             " IS ", " IS NOT ", " LIKE "}
            Me.DataLinkageStr = New String() {" AND ", " OR "}
            Me.DataFormulaStr = New String() {" GETDATE() ", " GETID() ", " NULL "}
            Me.IsUnicode = False
            Me.IsNullable = False
            Me.DataType = DBDataType.[String]
            Me.DataCompare = DBCompareType.Equal
            Me.DataLinkage = DBLinkage.[AND]
            Me.SortBy = False
            Me.ASC = True
            Me.Field = Field
            Me.Value = Value
            Me.DataType = DataType
        End Sub

        Public Sub New(ByVal Field As String, ByVal StringPattern As String, ByVal DataLinkage As DBLinkage)
            Me.Field = String.Empty
            Me.Value = String.Empty
            Me.StringPattern = String.Empty
            Me.DataCompareStr = New String() {" = ", " <> ", " > ", " < ", " >= ", " <= ", _
             " IS ", " IS NOT ", " LIKE "}
            Me.DataLinkageStr = New String() {" AND ", " OR "}
            Me.DataFormulaStr = New String() {" GETDATE() ", " GETID() ", " NULL "}
            Me.IsUnicode = False
            Me.IsNullable = False
            Me.DataType = DBDataType.[String]
            Me.DataCompare = DBCompareType.Equal
            Me.DataLinkage = DBLinkage.[AND]
            Me.SortBy = False
            Me.ASC = True
            Me.Field = Field
            Me.StringPattern = StringPattern
            Me.DataCompare = DBCompareType.[LIKE]
            Me.DataLinkage = DataLinkage
        End Sub

        Public Sub New(ByVal Field As String, ByVal Value As String, ByVal IsUnicode As Boolean)
            Me.Field = String.Empty
            Me.Value = String.Empty
            Me.StringPattern = String.Empty
            Me.DataCompareStr = New String() {" = ", " <> ", " > ", " < ", " >= ", " <= ", _
             " IS ", " IS NOT ", " LIKE "}
            Me.DataLinkageStr = New String() {" AND ", " OR "}
            Me.DataFormulaStr = New String() {" GETDATE() ", " GETID() ", " NULL "}
            Me.IsUnicode = False
            Me.IsNullable = False
            Me.DataType = DBDataType.[String]
            Me.DataCompare = DBCompareType.Equal
            Me.DataLinkage = DBLinkage.[AND]
            Me.SortBy = False
            Me.ASC = True
            Me.Field = Field
            Me.Value = Value
            Me.IsUnicode = IsUnicode
        End Sub

        Public Sub New(ByVal Field As String, ByVal DataFormula As DBDataFormula, ByVal DataLinkage As DBLinkage, ByVal DataCompare As DBCompareType)
            Me.Field = String.Empty
            Me.Value = String.Empty
            Me.StringPattern = String.Empty
            Me.DataCompareStr = New String() {" = ", " <> ", " > ", " < ", " >= ", " <= ", _
             " IS ", " IS NOT ", " LIKE "}
            Me.DataLinkageStr = New String() {" AND ", " OR "}
            Me.DataFormulaStr = New String() {" GETDATE() ", " GETID() ", " NULL "}
            Me.IsUnicode = False
            Me.IsNullable = False
            Me.DataType = DBDataType.[String]
            Me.DataCompare = DBCompareType.Equal
            Me.DataLinkage = DBLinkage.[AND]
            Me.SortBy = False
            Me.ASC = True
            Me.Field = Field
            Me.DataFormula = DataFormula
            Me.DataType = DBDataType.Formula
            Me.DataCompare = DataCompare
            Me.DataLinkage = DataLinkage
        End Sub

        Public Sub New(ByVal Field As String, ByVal TargetDateTime As DateTime, ByVal DataLinkage As DBLinkage, ByVal DataCompare As DBCompareType)
            Me.Field = String.Empty
            Me.Value = String.Empty
            Me.StringPattern = String.Empty
            Me.DataCompareStr = New String() {" = ", " <> ", " > ", " < ", " >= ", " <= ", _
             " IS ", " IS NOT ", " LIKE "}
            Me.DataLinkageStr = New String() {" AND ", " OR "}
            Me.DataFormulaStr = New String() {" GETDATE() ", " GETID() ", " NULL "}
            Me.IsUnicode = False
            Me.IsNullable = False
            Me.DataType = DBDataType.[String]
            Me.DataCompare = DBCompareType.Equal
            Me.DataLinkage = DBLinkage.[AND]
            Me.SortBy = False
            Me.ASC = True
            Me.Field = Field
            Me.Value = TargetDateTime.ToString()
            Me.DataType = DBDataType.[Date]
            Me.DataCompare = DataCompare
            Me.DataLinkage = DataLinkage
        End Sub

        Public Sub New(ByVal Field As String, ByVal Value As String, ByVal DataType As DBDataType, ByVal DataLinkage As DBLinkage)
            Me.Field = String.Empty
            Me.Value = String.Empty
            Me.StringPattern = String.Empty
            Me.DataCompareStr = New String() {" = ", " <> ", " > ", " < ", " >= ", " <= ", _
             " IS ", " IS NOT ", " LIKE "}
            Me.DataLinkageStr = New String() {" AND ", " OR "}
            Me.DataFormulaStr = New String() {" GETDATE() ", " GETID() ", " NULL "}
            Me.IsUnicode = False
            Me.IsNullable = False
            Me.DataType = DBDataType.[String]
            Me.DataCompare = DBCompareType.Equal
            Me.DataLinkage = DBLinkage.[AND]
            Me.SortBy = False
            Me.ASC = True
            Me.Field = Field
            Me.Value = Value
            Me.DataType = DataType
            Me.DataLinkage = DataLinkage
        End Sub

        Public Sub New(ByVal Field As String, ByVal Value As String, ByVal DataType As DBDataType, ByVal IsUnicode As Boolean)
            Me.Field = String.Empty
            Me.Value = String.Empty
            Me.StringPattern = String.Empty
            Me.DataCompareStr = New String() {" = ", " <> ", " > ", " < ", " >= ", " <= ", _
             " IS ", " IS NOT ", " LIKE "}
            Me.DataLinkageStr = New String() {" AND ", " OR "}
            Me.DataFormulaStr = New String() {" GETDATE() ", " GETID() ", " NULL "}
            Me.IsUnicode = False
            Me.IsNullable = False
            Me.DataType = DBDataType.[String]
            Me.DataCompare = DBCompareType.Equal
            Me.DataLinkage = DBLinkage.[AND]
            Me.SortBy = False
            Me.ASC = True
            Me.Field = Field
            Me.Value = Value
            Me.DataType = DataType
            Me.IsUnicode = IsUnicode
        End Sub

        Public Sub New(ByVal Field As String, ByVal Value As String, ByVal IsUnicode As Boolean, ByVal IsNullable As Boolean)
            Me.Field = String.Empty
            Me.Value = String.Empty
            Me.StringPattern = String.Empty
            Me.DataCompareStr = New String() {" = ", " <> ", " > ", " < ", " >= ", " <= ", _
             " IS ", " IS NOT ", " LIKE "}
            Me.DataLinkageStr = New String() {" AND ", " OR "}
            Me.DataFormulaStr = New String() {" GETDATE() ", " GETID() ", " NULL "}
            Me.IsUnicode = False
            Me.IsNullable = False
            Me.DataType = DBDataType.[String]
            Me.DataCompare = DBCompareType.Equal
            Me.DataLinkage = DBLinkage.[AND]
            Me.SortBy = False
            Me.ASC = True
            Me.Field = Field
            Me.Value = Value
            Me.IsUnicode = IsUnicode
            Me.IsNullable = IsNullable
        End Sub

        Public Sub New(ByVal Field As String, ByVal Value As String, ByVal DataType As DBDataType, ByVal DataLinkage As DBLinkage, ByVal DataCompare As DBCompareType)
            Me.Field = String.Empty
            Me.Value = String.Empty
            Me.StringPattern = String.Empty
            Me.DataCompareStr = New String() {" = ", " <> ", " > ", " < ", " >= ", " <= ", _
             " IS ", " IS NOT ", " LIKE "}
            Me.DataLinkageStr = New String() {" AND ", " OR "}
            Me.DataFormulaStr = New String() {" GETDATE() ", " GETID() ", " NULL "}
            Me.IsUnicode = False
            Me.IsNullable = False
            Me.DataType = DBDataType.[String]
            Me.DataCompare = DBCompareType.Equal
            Me.DataLinkage = DBLinkage.[AND]
            Me.SortBy = False
            Me.ASC = True
            Me.Field = Field
            Me.Value = Value
            Me.DataType = DataType
            Me.DataCompare = DataCompare
            Me.DataLinkage = DataLinkage
        End Sub

        Public Sub New(ByVal Field As String, ByVal Value As String, ByVal DataType As DBDataType, ByVal DataLinkage As DBLinkage, ByVal IsUnicode As Boolean)
            Me.Field = String.Empty
            Me.Value = String.Empty
            Me.StringPattern = String.Empty
            Me.DataCompareStr = New String() {" = ", " <> ", " > ", " < ", " >= ", " <= ", _
             " IS ", " IS NOT ", " LIKE "}
            Me.DataLinkageStr = New String() {" AND ", " OR "}
            Me.DataFormulaStr = New String() {" GETDATE() ", " GETID() ", " NULL "}
            Me.IsUnicode = False
            Me.IsNullable = False
            Me.DataType = DBDataType.[String]
            Me.DataCompare = DBCompareType.Equal
            Me.DataLinkage = DBLinkage.[AND]
            Me.SortBy = False
            Me.ASC = True
            Me.Field = Field
            Me.Value = Value
            Me.DataType = DataType
            Me.DataLinkage = DataLinkage
            Me.IsUnicode = IsUnicode
        End Sub

        Public Sub New(ByVal Field As String, ByVal Value As String, ByVal DBDataType As DBDataType, ByVal IsUnicode As Boolean, ByVal IsNullable As Boolean)
            Me.Field = String.Empty
            Me.Value = String.Empty
            Me.StringPattern = String.Empty
            Me.DataCompareStr = New String() {" = ", " <> ", " > ", " < ", " >= ", " <= ", _
             " IS ", " IS NOT ", " LIKE "}
            Me.DataLinkageStr = New String() {" AND ", " OR "}
            Me.DataFormulaStr = New String() {" GETDATE() ", " GETID() ", " NULL "}
            Me.IsUnicode = False
            Me.IsNullable = False
            Me.DataType = DBDataType.[String]
            Me.DataCompare = DBCompareType.Equal
            Me.DataLinkage = DBLinkage.[AND]
            Me.SortBy = False
            Me.ASC = True
            Me.Field = Field
            Me.Value = Value
            Me.DataType = DBDataType
            Me.IsUnicode = IsUnicode
            Me.IsNullable = IsNullable
        End Sub

        Public Sub New(ByVal Field As String, ByVal Value As String, ByVal DataType As DBDataType, ByVal DataLinkage As DBLinkage, ByVal IsUnicode As Boolean, ByVal IsNullable As Boolean)
            Me.Field = String.Empty
            Me.Value = String.Empty
            Me.StringPattern = String.Empty
            Me.DataCompareStr = New String() {" = ", " <> ", " > ", " < ", " >= ", " <= ", _
             " IS ", " IS NOT ", " LIKE "}
            Me.DataLinkageStr = New String() {" AND ", " OR "}
            Me.DataFormulaStr = New String() {" GETDATE() ", " GETID() ", " NULL "}
            Me.IsUnicode = False
            Me.IsNullable = False
            Me.DataType = DBDataType.[String]
            Me.DataCompare = DBCompareType.Equal
            Me.DataLinkage = DBLinkage.[AND]
            Me.SortBy = False
            Me.ASC = True
            Me.Field = Field
            Me.Value = Value
            Me.DataType = DataType
            Me.DataLinkage = DataLinkage
            Me.IsUnicode = IsUnicode
            Me.IsNullable = IsNullable
        End Sub
#End Region
    End Class

    Public Class DatabaseParameters
        Inherits CollectionBase
#Region "Fields"
        Public DataLinkage As DBLinkage
#End Region

#Region "Properties"
        Default Public Property Item(ByVal index As Integer) As DatabaseParameter
            Get
                Return DirectCast(MyBase.List(index), DatabaseParameter)
            End Get
            Set(ByVal value As DatabaseParameter)
                MyBase.List(index) = value
            End Set
        End Property
#End Region

#Region "Methods"
        Public Sub New()
            Me.DataLinkage = DBLinkage.[AND]
        End Sub

        Public Sub New(ByVal DataLinkage As DBLinkage)
            Me.DataLinkage = DBLinkage.[AND]
            Me.DataLinkage = DataLinkage
        End Sub

        Public Function Add(ByVal value As DatabaseParameter) As Integer
            Return MyBase.List.Add(value)
        End Function

        Public Function IndexOf(ByVal value As DatabaseParameter) As Integer
            Return MyBase.List.IndexOf(value)
        End Function

        Public Sub Insert(ByVal index As Integer, ByVal value As DatabaseParameter)
            MyBase.List.Insert(index, value)
        End Sub

        Public Sub Remove(ByVal value As DatabaseParameter)
            MyBase.List.Remove(value)
        End Sub
#End Region
    End Class

    Public Class DatabaseParametersGroup
        Inherits CollectionBase
#Region "Fields"
        Public DataLinkage As DBLinkage
#End Region

#Region "Properties"
        Default Public Property Item(ByVal index As Integer) As DatabaseParameters
            Get
                Return DirectCast(MyBase.List(index), DatabaseParameters)
            End Get
            Set(ByVal value As DatabaseParameters)
                MyBase.List(index) = value
            End Set
        End Property
#End Region

#Region "Methods"
        Public Sub New()
            Me.DataLinkage = DBLinkage.[AND]
        End Sub

        Public Sub New(ByVal DataLinkage As DBLinkage)
            Me.DataLinkage = DBLinkage.[AND]
            Me.DataLinkage = DataLinkage
        End Sub

        Public Function Add(ByVal value As DatabaseParameters) As Integer
            Return MyBase.List.Add(value)
        End Function

        Public Function IndexOf(ByVal value As DatabaseParameters) As Integer
            Return MyBase.List.IndexOf(value)
        End Function

        Public Sub Insert(ByVal index As Integer, ByVal value As DatabaseParameters)
            MyBase.List.Insert(index, value)
        End Sub

        Public Sub Remove(ByVal value As DatabaseParameters)
            MyBase.List.Remove(value)
        End Sub
#End Region
    End Class

#Region "Helper methods"
    Private Shared Function GetTransactionSqlConnection(ByVal connectionString As String) As SqlConnection
        Dim namedDataSlot As LocalDataStoreSlot = Thread.GetNamedDataSlot("ConnectionDictionary")
        Dim data As Dictionary(Of String, SqlConnection) = DirectCast(Thread.GetData(namedDataSlot), Dictionary(Of String, SqlConnection))
        If data Is Nothing Then
            data = New Dictionary(Of String, SqlConnection)()
            Thread.SetData(namedDataSlot, data)
        End If
        Dim connection As SqlConnection = Nothing
        If data.ContainsKey(connectionString) Then
            Return data(connectionString)
        End If
        connection = New SqlConnection(connectionString)
        data.Add(connectionString, connection)
        AddHandler System.Transactions.Transaction.Current.TransactionCompleted, AddressOf Current_TransactionCompleted
        Return (connection)
    End Function

    Private Shared Sub Current_TransactionCompleted(ByVal sender As Object, ByVal e As TransactionEventArgs)
        Dim data As Dictionary(Of String, SqlConnection) = DirectCast(Thread.GetData(Thread.GetNamedDataSlot("ConnectionDictionary")), Dictionary(Of String, SqlConnection))
        If data IsNot Nothing Then
            For Each connection As SqlConnection In data.Values
                If (connection IsNot Nothing) AndAlso (connection.State <> ConnectionState.Closed) Then
                    connection.Close()
                End If
            Next
            data.Clear()
        End If
        Thread.FreeNamedDataSlot("ConnectionDictionary")
    End Sub

    Private Shared Function CreateCommand(ByVal connection As SqlConnection, ByVal commandType As CommandType, ByVal commandText As String) As SqlCommand
        If (connection IsNot Nothing) AndAlso (connection.State = ConnectionState.Closed) Then
            connection.Open()
        End If
        Using command As New SqlCommand()
            command.Connection = connection
            command.CommandText = commandText
            command.CommandType = commandType
            Return command
        End Using
    End Function

    Private Shared Function CreateCommand(ByVal connection As SqlConnection, ByVal commandType As CommandType, ByVal commandText As String, ByVal ParamArray values As Object()) As SqlCommand
        If (connection IsNot Nothing) AndAlso (connection.State = ConnectionState.Closed) Then
            connection.Open()
        End If
        Using command As New SqlCommand()
            command.Connection = connection
            command.CommandText = commandText
            command.CommandType = commandType
            If (values Is Nothing) OrElse (values.Length = 0) Then
                For j As Integer = 0 To command.Parameters.Count - 1
                    command.Parameters(j).Value = DBNull.Value
                Next
                Return command
            End If
            command.Parameters.AddRange(values)
            Return command
        End Using
    End Function

    Public Shared Function InsertCommand(ByVal keys As DatabaseParameters, ByVal table As String) As String
        Dim builder As New StringBuilder()
        Dim builder2 As New StringBuilder()

        For i As Integer = 0 To keys.Count - 1
            builder.Append(keys(i).FieldName & ",")
            builder2.Append((If(((keys(i).ParamType = DBDataType.Formula) OrElse (keys(i).ParamType = DBDataType.SQLFormula)), keys(i).FieldValue, (If(((keys(i).FieldValue.Length > 0)), ((If(keys(i).IsUnicode, "N", "")) & "'" & keys(i).FieldValue & "'"), (If(keys(i).IsNullable, "NULL", "''")))))) & ",")
        Next
        builder.Remove(builder.Length - 1, 1)
        builder2.Remove(builder2.Length - 1, 1)
        Return "INSERT INTO " & table & " ( " & builder.ToString() & " ) VALUES (" & builder2.ToString() & ")"
    End Function

    Public Shared Function UpdateCommand(ByVal keys As DatabaseParameters, ByVal values As DatabaseParameters, ByVal table As String) As String
        Dim num As Integer
        Dim builder As New StringBuilder("UPDATE " & table & " SET ")
        If values IsNot Nothing Then
            For num = 0 To values.Count - 1
                builder.Append(values(num).FieldName & " = " & (If(((values(num).ParamType = DBDataType.Formula) OrElse (values(num).ParamType = DBDataType.SQLFormula)), values(num).FieldValue, (If((values(num).FieldValue.Length > 0), ((If(values(num).IsUnicode, "N", "")) & "'" & values(num).FieldValue & "'"), (If(values(num).IsNullable, "NULL", "''")))))) & ",")
            Next
        End If
        builder.Remove(builder.Length - 1, 1)
        If keys IsNot Nothing Then
            For num = 0 To keys.Count - 1
                builder.Append(If((num = 0), " WHERE ", (" " & keys(num).ParamLinkage.ToString() & " ")))
                builder.Append(If(((keys(num).ParamType = DBDataType.Formula) OrElse (keys(num).ParamType = DBDataType.SQLFormula)), (keys(num).FieldName & " = " & keys(num).FieldValue), (keys(num).FieldName & " = '" & keys(num).FieldValue & "'")))
            Next
        End If
        Return builder.ToString()
    End Function

    Public Shared Function DeleteCommand(ByVal keys As DatabaseParameters, ByVal table As String) As String
        Dim builder As New StringBuilder("DELETE FROM " & table & " ")
        If keys IsNot Nothing Then
            For i As Integer = 0 To keys.Count - 1
                builder.Append(If((i = 0), " WHERE ", (" " & keys(i).ParamLinkage.ToString() & " ")))
                builder.Append(If(((keys(i).ParamType = DBDataType.Formula) OrElse (keys(i).ParamType = DBDataType.SQLFormula)), (keys(i).FieldName & keys(i).ParamCompare & keys(i).FieldValue), (keys(i).FieldName & keys(i).ParamCompare & "'" & keys(i).FieldValue & "'")))
            Next
        End If
        Return builder.ToString()

    End Function

    Public Shared Sub SelectCommand(ByVal Parameters As DatabaseParameters, ByVal TableNames__1 As String)
        Dim parametersGroup As New DatabaseParametersGroup()
        parametersGroup.Add(Parameters)
        Dim tableNames__2 As String() = New String() {TableNames__1}
        SelectCommand(parametersGroup, tableNames__2)
    End Sub

    Public Shared Function SelectCommand(ByVal ParametersGroup As DatabaseParametersGroup, ByVal TableNames As String()) As String
        Dim num As Integer
        Dim builder As New StringBuilder("SELECT * FROM ")
        Dim builder2 As New StringBuilder()
        For num = 0 To TableNames.Length - 1
            builder.Append(TableNames(num) & ",")
        Next
        builder.Remove(builder.Length - 1, 1)
        For num = 0 To ParametersGroup.Count - 1
            If ParametersGroup(num).Count > 0 Then
                builder.Append(If((num = 0), " WHERE ", (" " & ParametersGroup(num).DataLinkage.ToString() & " ")))
                builder.Append(" (")
                For i As Integer = 0 To ParametersGroup(num).Count - 1
                    builder.Append(If((i = 0), "", (" " & ParametersGroup(num)(i).ParamLinkage.ToString() & " ")))
                    builder.Append(If(((ParametersGroup(num)(i).ParamType = DBDataType.SQLFormula) OrElse (ParametersGroup(num)(i).ParamType = DBDataType.Formula)), (ParametersGroup(num)(i).FieldName & ParametersGroup(num)(i).ParamCompare & ParametersGroup(num)(i).FieldValue), (ParametersGroup(num)(i).FieldName & " " & ParametersGroup(num)(i).ParamCompare & " '" & ParametersGroup(num)(i).FieldValue & "'")))
                    If ParametersGroup(num)(i).SortBy Then
                        builder2.Append(ParametersGroup(num)(i).FieldName & " " & (If(ParametersGroup(num)(i).ASC, "ASC", "DESC")) & ", ")
                    End If
                Next
                builder.Append(" )")
            End If
        Next
        If builder2.Length > 0 Then
            builder2.Remove(builder2.Length - 1, 1)
            builder.Append(" ORDER BY " & builder2.ToString())
        End If
        Return builder.ToString()
    End Function

    Public Shared Function CreateDataSet(ByVal command As SqlCommand) As DataSet
        Using adapter As New SqlDataAdapter(command)
            Dim dataSet As New DataSet()
            adapter.Fill(dataSet)
            Return dataSet
        End Using
    End Function

    Public Shared Function CreateDataTable(ByVal command As SqlCommand) As DataTable
        Using adapter As New SqlDataAdapter(command)
            Dim dataTable As New DataTable()
            adapter.Fill(dataTable)
            Return dataTable
        End Using
    End Function
#End Region
End Class
